Release 10.1A: OpenEdge Development:
Debugging and Troubleshooting

Query statistics

Query statistics provide information on the query as it extracts data from the database, that is, on the retrieval of records during the query. This information is intended to help you evaluate query performance.

For the purposes of gathering statistics, queries fall into either of two categories:

Pre-pass queries — Queries that build a result-list completely, in one 4GL statement, before going on to the next 4GL statement. OpenEdge builds the result-list at different times for different types of queries. These queries include:
Static and dynamic queries that perform sorting or pre-fetching. (OpenEdge builds a complete result-list only on the first GET NEXT statement or GET-NEXT() method executed.)
Static PRESELECT statements. OpenEdge builds a complete result-list on the PRESELECT statement.
FOR statements that sort on the client and build a list of results in a sort file. OpenEdge builds a complete result-list on the FOR EACH statement.
Non-pre-pass queries — Queries that do not build a result-list completely before executing the next 4GL statement. These queries include FOR EACH statements and queries that sort by an index, enabling the server to identify the next record from the index, without any sorting by the client.

Between record retrievals, the client steps through 4GL statements in an iterating block, for example, between a FOR EACH and its corresponding END statement. In cases where the client is selecting the records, only fetches involving records that satisfy selection criteria iterate through the loop.

Query Info logging logs different information depending on whether the query is a pre-pass query or a non-pre-pass query. Both pre-pass and non-pre-pass queries write statistics when the query is complete. A query is considered complete at the following points in the code:

For FOR and static PRESELECT statements, at the end of the block (the END statement). Any statements that cause a premature exit from the block still result in end-of-block processing, and the logging of query statistics.
For static and dynamic queries, when OpenEdge closes the query. The following are examples of when OpenEdge closes the query:
On a CLOSE QUERY statement or QUERY-CLOSE() method.
On a OPEN QUERY statement or QUERY-OPEN() method if the query is already open.
When the query goes out of scope, that is, the program containing the query exits.
When a program deletes a buffer used by a dynamically opened query.

Query statistics for pre-pass queries

OpenEdge provides two sets of query statistics for pre-pass queries: first, when OpenEdge builds the result-list; second when the query is complete, that is, at the end of the block, or when OpenEdge closes the query.

OpenEdge logs the following query statistics when it completely builds the result-list for a pre-pass query:

Procedure name and line number where OpenEdge built the result-list.
Query ID, the query's unique identifier.
For static and dynamic queries, a string representation of the query handle, and the name of the query object (if available).
Number of entries in the result-list.
Time taken to build the result-list. See the "Time taken to build a result-list" section.
Database block access required to build the result-list. See the "Database block access" section.
Table and index read access statistics. See the "Read Access Statistics" section.
Number of records for each table in the query that were sent to the client to resolve the query. See the "Number of records sent by the server" section.
Field list used to optimize the query. See the "Field list information" section.
Whether the client rather than the server performs selection of records for each table in the query.
Number of times OpenEdge has opened this query. A program can reuse static and dynamic query objects. Opening a query multiple times, as within a loop, might indicate poor programming practice.
For dynamically opened queries, query preparation statistics. See the "Query preparation statistics" section.

OpenEdge logs the following query statistics when the pre-pass query is complete:

Procedure name and line number where the block ends or OpenEdge closes the query.
Query ID, the query's unique identifier.
For statically and dynamically opened queries, a string representation of the query handle, and the name of the query object (if available).
Database block access required to retrieve the 4GL records. Not logged for queries on temp-tables. See the "Database block access" section.
Table and index read access statistics. See the "Read Access Statistics" section.
Number of records per table made available to the 4GL. See the "Number of records available to the 4GL" section.
Whether the program used INDEX-REPOSITION on the query.

Query statistics for non-pre-pass queries

OpenEdge provides a single set of statistics for non-pre-pass queries at query completion. Query Info logging logs the following query statistics when the query is complete:

Procedure name and line number where the block ends or where OpenEdge closes the query.
Query ID, the query's unique identifier.
For dynamic or static queries, a string representation of the query handle, and the name of the query object (if available).
Database block access required to resolve the query. Not logged for queries on temp-tables. See the "Database block access" section.
Table and index read access statistics. See the "Read Access Statistics" section.
Field list used to optimize the query. See the "Field list information" section.
Number of records for each table in the query that were available to the 4GL. See the "Number of records available to the 4GL" section.
Number of records for each table in the query that were sent to the client to resolve the query. See the "Number of records sent by the server" section.
Whether the client rather than the server performs selection of records for each table in the query.
For dynamic or static queries, number of times OpenEdge has opened this query.
For dynamically opened queries, query preparation statistics. See the "Query preparation statistics" section.
For scrolling queries, whether the program used INDEX-REPOSITION on the query.

Query preparation statistics

Query preparation determines how OpenEdge will access the requested data, for example, which indices to use. The compiler prepares statically opened queries at compile time. OpenEdge prepares dynamically opened queries at run time, using the QUERY-PREPARE() method.

Because preparation of a dynamically opened query can have an impact on performance, Query Info logging tracks the following statistics on dynamically opened queries:

Number of times OpenEdge has prepared this query.
Time taken to prepare the query (in milliseconds).
Database block access required to prepare the query.

Number of records sent by the server

The server, in resolving a query, sends a number of records to the client. In certain cases, the client determines whether a record satisfies selection criteria. If a query returns a large number of records, only a few of which the client determines satisfy the selection criteria, the query might be inefficient.

For each table in a query, Query Info logging tracks:

Number of records sent to the client. For self-service and single-user database connections, this refers to the number of records selected by the database engine.
Number of records that satisfied the criteria.
Number of records that failed the criteria.

Optimizations within the client and database server might skew the figures somewhat, as in the cases when the server returns to the client field lists rather than a full record, or a prefetched list of records.

Use these figures with other statistical data to determine if a query is inefficient.

Number of records available to the 4GL

It is useful to know the number of records available to the 4GL:

In the context of a FOR block or a REPEAT loop on a query, this is the number of records the 4GL program accessed through a buffer.
For a browse object on a static or dynamic query, this is the number of records fetched to populate the browse.

Consider this figure in light of other statistical data, such as blocks accessed, or records sent by the server, to determine the efficiency of the query. For example:

If used in a FOR or static PRESELECT block with no repositioning, the figure accurately reflects the number of records that satisfy the search criteria:

FOR EACH customer WHERE cust-num < 5

There are four customers.

If a program repositions the query, or leaves a block before it fetches all records that satisfy the criteria, the figure is not a true measure of the number of matching records.

Time taken to build a result-list

It is useful to know how long it takes to build a result-list for a pre-pass query, in evaluating query efficiency. Using this value in combination with other statistical data, such as the number of entries in a result-list, the number of records read, and database block access, you can calculate your own query averages, for example, average time a record is available to the 4GL, or average time for a record to be sent to the client.

Field list information

Defining field lists in a query improves performance to remote databases and dataservers. The 4GL compiler may further optimize the query by adding implicit fields to the field lists or use an implicit field list if the user did not specify one. Query Info logging logs all field lists a query uses as part of the Query Statistics. Each field list is logged with the table to which it applies.

For more information on field lists, see OpenEdge Development: Programming Interfaces .

Database block access

When evaluating query efficiency, it is useful to consider the number of times the server performs database block accesses on behalf of the client. A disparity in the number of accesses to records returned could indicate poor index selection. OpenEdge tracks database block access on OpenEdge RDBMSs (but not on dataservers) in the _userio-dbaccess field of the _userio Virtual System Table:

For pre-pass queries, the number of blocks accessed is the difference between the number of block accesses immediately before the query starts building the result-list, and the number immediately after the query finishes building the list.
For non-pre-pass queries, and for the reading of 4GL records in pre-pass queries, the number of blocks accessed is the accumulation of accesses before and after each record fetch executed by the query.

A large number of database block accesses do not necessarily indicate a poorly conceived query. The number stored in the _userio-dbaccess field is a combined figure that includes both RM (data) and index block accesses. Although a high count on index accesses might predictably indicate inefficiencies in index use, a high count on RM accesses might simply mean that records are scattered throughout the database rather than in contiguous blocks. Thus, you have to weigh this value with other statistical data to determine query efficiency.

Note: Although not recommended, it is possible for a query to access tables in more than one database. For any such queries, OpenEdge records block access statistics for each database accessed. OpenEdge does not, however, track block access statistics for databases in single-user mode.

Read Access Statistics

Query Info Logging captures table and index read access statistics for each table and index identified in a query against an OpenEdge database. Read access statistics includes reads by all users connected to the database.

Note: Query Info logging omits read access statistics for DataServers and temp-tables. Additionally, read access statistics are not logged for preparing a dynamic query.

Table read access statistics provide the number of times read access has occurred on a table. Use Base Table (-basetable) with Table Range Size (-tablerangesize) to specify the range of tables for which you want to collect statistics. Table statistics are stored in the _TableStat-Read field of the _TableStat Virtual System Tables (VST).

Index read access statistics provide the number of times read access has occurred on an index. Use Base Index (-baseindex) with Index Range Size (-indexrangesize) to specify the range of indexes for which you want to collect statistics. Index statistics are stored in the _IndexStat-Read field of the _IndexStat VST.

Read access statistics information is located in the Query Statistics after the DB Block access statistics. For each table in the query, or a non-pre-pass query, the client writes the read access statistics in the Query Statistics once the query completes. For a pre-pass query, the client writes the read access statistics in the Query Statistics for building the results list, and again once the query completes. This differentiates the read accesses required to build the result list from those required to iterate through the list.

In order to get the table and index read statistics, the user must ensure the queried tables and indices are within the ranges specified by the startup parameters Base Table (-basetable)/Table Range Size (-tablerangesize) and Base Index (-baseindex)/Index Range Size (-indexrangesize) ranges. If your query accesses tables or indices outside these ranges, Query Info logging cannot determine the access statistics for the tables or indices, and displays “UNAVAILABLE” for the read access statistics.

For more information on the referenced startup parameters, see OpenEdge Deployment: Startup Command and Parameter Reference . For more information on VSTs, see OpenEdge Data Management: Database Administration .